<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="UTF-8">
<meta name="viewport"
      content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">

    <meta name="author" content="xxx">




    <meta name="keywords" content="博客">




<title>MyCat | 小站</title>



    <link rel="icon" href="/favicon.ico">



<style>
    @import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&family=Roboto+Mono&display=swap');
</style>



    <!-- stylesheets list from _config.yml -->
    
    <link rel="stylesheet" href="/css/style.css">
    




    <!-- scripts list from _config.yml -->
    
    <script src="/js/frame.js"></script>
    







    <script src='https://unpkg.com/valine@1.4.16/dist/Valine.min.js'></script>




  <meta name="generator" content="Hexo 6.0.0"></head>
  <body>
    <div class="mask-border">
    </div>

    <div class="wrapper">

      <div class="header">
  <div class="flex-container">
    <div class="header-inner">
      <div class="site-brand-container">
        <a href="/">
          
            Wudiguang.
          
        </a>
      </div>
      <div id="menu-btn" class="menu-btn" onclick="toggleMenu()">
        Menu
      </div>
      <nav class="site-nav">
        <ul class="menu-list">
          
            
              <li class="menu-item">
                <a href="/">Home</a>
              </li> 
                   
          
            
              <li class="menu-item">
                <a href="/tag/">标签</a>
              </li> 
                   
          
            
              <li class="menu-item">
                <a href="/archives/">时间线</a>
              </li> 
                   
          
            
              <li class="menu-item">
                <a href="/categories/gallery/">展览馆</a>
              </li> 
                   
          
            
              <li class="menu-item">
                <a href="/comment/">留言</a>
              </li> 
                   
          
        </ul>
      </nav>
    </div>
  </div>
</div>


      <div class="main">
        <div class="flex-container">
          <article id="post">

  
    <div class="post-head">
    <div class="post-info">
        <div class="tag-list">
            
                
                    <span class="post-tag">
                        <a href="/tags/mysql/">
                            mysql
                        </a>
                    </span>    
                
                    <span class="post-tag">
                        <a href="/tags/mycat/">
                            mycat
                        </a>
                    </span>    
                           
            
        </div>
        <div class="post-title">
            
            
                MyCat
            
            
        </div>
        <span class="post-date">
            5月 16, 2022
        </span>
    </div>
    <div class="post-img">
        
            <div class="h-line-primary"></div>
              
    </div>
</div>
    <div class="post-content">
    <h2 id="MyCat-实现高可用读写分离分库分表"><a href="#MyCat-实现高可用读写分离分库分表" class="headerlink" title="MyCat 实现高可用读写分离分库分表"></a>MyCat 实现高可用读写分离分库分表</h2><blockquote>
<p>拦截（SQL 拦截分析&amp;查询结果拦截返回）</p>
</blockquote>
<ul>
<li>mycat：主从复制，读写分离</li>
<li>mycat-web：mycat 可视化界面</li>
<li>HAProxy：mycat 集群</li>
<li>HAProxy + Keepalived：mycat 高可用集群架构</li>
</ul>
<p><code>注</code></p>
<ul>
<li>keepalived：作用是抢占vip（虚拟IP）以提供对外服务</li>
<li>Mycat 的高可用及负载均衡由 HAProxy 来实现，而 HAProxy 的高可用由 keepalived 实现</li>
</ul>
<h2 id="MyCat-配置文件"><a href="#MyCat-配置文件" class="headerlink" title="MyCat 配置文件"></a>MyCat 配置文件</h2><ul>
<li>schema.xml：MyCat 的逻辑库、逻辑表以及对应的分片规则、DataNode以及 DataSourcec</li>
<li>server.xml：MyCat 系统配置信息。如用户名、密码及权限</li>
<li>rule.xml：拆分表的规则</li>
</ul>
<p><code>MyCat 管理端口：9066，服务端口：8066</code></p>
<span id="more"></span>

<h3 id="schema-xml-配置"><a href="#schema-xml-配置" class="headerlink" title="schema.xml 配置"></a>schema.xml 配置</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"><span class="meta">&lt;?xml version=&quot;1.0&quot;?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="keyword">mycat</span>:schema <span class="keyword">SYSTEM</span> <span class="string">&quot;schema.dtd&quot;</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:schema</span> <span class="attr">xmlns:mycat</span>=<span class="string">&quot;http://io.mycat/&quot;</span>&gt;</span></span><br><span class="line"></span><br><span class="line">	<span class="comment">&lt;!-- 1.schema：定义MyCat实例中的逻辑库 --&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">schema</span> <span class="attr">name</span>=<span class="string">&quot;TESTDB&quot;</span> <span class="attr">checkSQLschema</span>=<span class="string">&quot;true&quot;</span> <span class="attr">sqlMaxLimit</span>=<span class="string">&quot;100&quot;</span>&gt;</span></span><br><span class="line">		<span class="comment">&lt;!-- 2.auto-sharding-long的分片规则是按ID值的范围进行分片--&gt;</span> </span><br><span class="line">        <span class="comment">&lt;!-- 1-5000000 为第1片 5000001-10000000 为第2片...--&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;travelrecord&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1,dn2,dn3&quot;</span> <span class="attr">rule</span>=<span class="string">&quot;auto-sharding-long&quot;</span> /&gt;</span>	</span><br><span class="line">	<span class="tag">&lt;/<span class="name">schema</span>&gt;</span></span><br><span class="line">	</span><br><span class="line">	<span class="comment">&lt;!-- 3.定义了MyCat中的数据节点,即数据分片 --&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">&quot;dn1&quot;</span> <span class="attr">dataHost</span>=<span class="string">&quot;localhost1&quot;</span> <span class="attr">database</span>=<span class="string">&quot;db1&quot;</span> /&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">&quot;dn2&quot;</span> <span class="attr">dataHost</span>=<span class="string">&quot;localhost1&quot;</span> <span class="attr">database</span>=<span class="string">&quot;db2&quot;</span> /&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">&quot;dn3&quot;</span> <span class="attr">dataHost</span>=<span class="string">&quot;localhost1&quot;</span> <span class="attr">database</span>=<span class="string">&quot;db3&quot;</span> /&gt;</span></span><br><span class="line">	</span><br><span class="line">	<span class="comment">&lt;!-- 4.定义数据库实例，读写分离配置和心跳语句 --&gt;</span></span><br><span class="line">	<span class="comment">&lt;!-- balance 0不开启读写分离；1读写分离...--&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">&quot;localhost1&quot;</span> <span class="attr">maxCon</span>=<span class="string">&quot;1000&quot;</span> <span class="attr">minCon</span>=<span class="string">&quot;10&quot;</span> <span class="attr">balance</span>=<span class="string">&quot;0&quot;</span></span></span><br><span class="line"><span class="tag">			  <span class="attr">writeType</span>=<span class="string">&quot;0&quot;</span> <span class="attr">dbType</span>=<span class="string">&quot;mysql&quot;</span> <span class="attr">dbDriver</span>=<span class="string">&quot;native&quot;</span> <span class="attr">switchType</span>=<span class="string">&quot;1&quot;</span>  <span class="attr">slaveThreshold</span>=<span class="string">&quot;100&quot;</span>&gt;</span>		  </span><br><span class="line">		<span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">&quot;hostM1&quot;</span> <span class="attr">url</span>=<span class="string">&quot;localhost:3306&quot;</span> <span class="attr">user</span>=<span class="string">&quot;root&quot;</span> <span class="attr">assword</span>=<span class="string">&quot;123456&quot;</span>&gt;</span><span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span></span><br><span class="line">	</span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:schema</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h3 id="server-xml"><a href="#server-xml" class="headerlink" title="server.xml"></a>server.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"><span class="meta">&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="keyword">mycat</span>:server <span class="keyword">SYSTEM</span> <span class="string">&quot;server.dtd&quot;</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:server</span> <span class="attr">xmlns:mycat</span>=<span class="string">&quot;http://io.mycat/&quot;</span>&gt;</span></span><br><span class="line"></span><br><span class="line">	<span class="comment">&lt;!--1.系统参数设置--&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">system</span>&gt;</span></span><br><span class="line">		...</span><br><span class="line">		<span class="comment">&lt;!-- 20210417 设置字符集 --&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;charset&quot;</span>&gt;</span>utf8<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">system</span>&gt;</span></span><br><span class="line">	</span><br><span class="line">	<span class="comment">&lt;!--2.用户信息设置--&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">user</span> <span class="attr">name</span>=<span class="string">&quot;root&quot;</span> <span class="attr">defaultAccount</span>=<span class="string">&quot;true&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;password&quot;</span>&gt;</span>123456<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;schemas&quot;</span>&gt;</span>ITCAST<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">user</span>&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:server</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h3 id="rule-xml"><a href="#rule-xml" class="headerlink" title="rule.xml"></a>rule.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"><span class="meta">&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="keyword">mycat</span>:rule <span class="keyword">SYSTEM</span> <span class="string">&quot;rule.dtd&quot;</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:rule</span> <span class="attr">xmlns:mycat</span>=<span class="string">&quot;http://io.mycat/&quot;</span>&gt;</span></span><br><span class="line">	</span><br><span class="line">	<span class="comment">&lt;!-- 1.tableRule配置规则 --&gt;</span></span><br><span class="line">	<span class="comment">&lt;!-- 分片的字段为&quot;id&quot;,分片规则是&quot;rang-long&quot; --&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">tableRule</span> <span class="attr">name</span>=<span class="string">&quot;auto-sharding-long&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">rule</span>&gt;</span></span><br><span class="line">			<span class="tag">&lt;<span class="name">columns</span>&gt;</span>id<span class="tag">&lt;/<span class="name">columns</span>&gt;</span></span><br><span class="line">			<span class="tag">&lt;<span class="name">algorithm</span>&gt;</span>rang-long<span class="tag">&lt;/<span class="name">algorithm</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;/<span class="name">rule</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">tableRule</span>&gt;</span></span><br><span class="line">	</span><br><span class="line">	<span class="comment">&lt;!-- 2.&quot;rang-long&quot;的规则具体映射文件是&quot;autopartition-long.txt&quot; --&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">function</span> <span class="attr">name</span>=<span class="string">&quot;rang-long&quot;</span></span></span><br><span class="line"><span class="tag">		<span class="attr">class</span>=<span class="string">&quot;io.mycat.route.function.AutoPartitionByLong&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;mapFile&quot;</span>&gt;</span>autopartition-long.txt<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">function</span>&gt;</span></span><br><span class="line">	</span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:rule</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h2 id="win-系统下使用-MyCat"><a href="#win-系统下使用-MyCat" class="headerlink" title="win 系统下使用 MyCat"></a>win 系统下使用 MyCat</h2><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">#</span><span class="language-bash"><span class="comment"># cmd使用管理员打开并安装</span></span></span><br><span class="line">mycat.bat install</span><br><span class="line"><span class="meta"></span></span><br><span class="line"><span class="meta">#</span><span class="language-bash"><span class="comment"># 启动mycat服务</span></span></span><br><span class="line">mycat.bat start</span><br><span class="line"><span class="meta"></span></span><br><span class="line"><span class="meta">#</span><span class="language-bash"><span class="comment"># 查看mycat的运行状态：</span></span></span><br><span class="line">mycat.bat status</span><br><span class="line"><span class="meta"></span></span><br><span class="line"><span class="meta">#</span><span class="language-bash"><span class="comment"># 停止mycat服务</span></span></span><br><span class="line">mycat.bat stop</span><br></pre></td></tr></table></figure>

<h3 id="性能监控（MyCat-Web）"><a href="#性能监控（MyCat-Web）" class="headerlink" title="性能监控（MyCat-Web）"></a>性能监控（MyCat-Web）</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">MyCat-Web 引入 zk 作为配置中心，可以管理多个节点</span><br><span class="line"></span><br><span class="line">1. 安装 zk</span><br><span class="line">2. 启动 MyCat-Web</span><br><span class="line">3. http://localhost:8082/mycat</span><br><span class="line">4. 配置 MyCat 参数</span><br></pre></td></tr></table></figure>

<h3 id="MyCat-和-MyCat-Web-的关系图"><a href="#MyCat-和-MyCat-Web-的关系图" class="headerlink" title="MyCat 和 MyCat-Web 的关系图"></a>MyCat 和 MyCat-Web 的关系图</h3><p><code>流程：</code></p>
<ol>
<li>Java Web 项目触发数据查询（select * from user where status&#x3D;’0’）</li>
<li>MyCat Server（解析 SQL -&gt; 分片分析 -&gt; 路由分析 -&gt; 读写分离分析 -&gt; …）</li>
<li>定位到x库*表</li>
<li>MyCat Server（结果合并 -&gt; 聚合处理 -&gt; 排序处理 -&gt; 分页处理 -&gt; …）</li>
</ol>
<h2 id="MyCat-高可用架构"><a href="#MyCat-高可用架构" class="headerlink" title="MyCat 高可用架构"></a>MyCat 高可用架构</h2><h3 id="MyCat-主从读写分离"><a href="#MyCat-主从读写分离" class="headerlink" title="MyCat 主从读写分离"></a>MyCat 主从读写分离</h3><p>一主一从</p>
<h4 id="MyCat-集群部署"><a href="#MyCat-集群部署" class="headerlink" title="MyCat 集群部署"></a>MyCat 集群部署</h4><p>HAProxy 实现 MyCat 高可用</p>
<h4 id="MyCat-高可用集群部署"><a href="#MyCat-高可用集群部署" class="headerlink" title="MyCat 高可用集群部署"></a>MyCat 高可用集群部署</h4><p>keepalived 实现 HAProxy 高可用</p>
<h3 id="双主双从读写分离"><a href="#双主双从读写分离" class="headerlink" title="双主双从读写分离"></a>双主双从读写分离</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">双主双从：</span><br><span class="line">    架构    ：m1 -&gt; s1   ;  m2 -&gt; s2    ;  m1 &lt;-&gt; m2 互为主备</span><br><span class="line">    读写分离 ：m1写       ;  m2,s1,s2读</span><br><span class="line">    挂机    ：m1挂掉后m2进行写</span><br><span class="line">    </span><br><span class="line">mycat主从切换问题：</span><br><span class="line">    方式1：自动切换：</span><br><span class="line">    当M宕机后， 读写S ； 恢复M后， 写S， 读M ；</span><br><span class="line">    当S宕机后， 读写M ； 恢复S后， 写M， 读S ；</span><br><span class="line">    方式2：基于MySQL主从同步状态的切换</span><br><span class="line">    MyCat检测到主从数据同步延迟，会自动切换到拥有最新数据的MySQL服务器</span><br></pre></td></tr></table></figure>

<h3 id="MyCat-分库分表架构图"><a href="#MyCat-分库分表架构图" class="headerlink" title="MyCat 分库分表架构图"></a>MyCat 分库分表架构图</h3><h2 id="案例"><a href="#案例" class="headerlink" title="案例"></a>案例</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">url: jdbc:mysql://127.0.0.1:8066/V_SHOP?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC</span><br></pre></td></tr></table></figure>

<h3 id="schema-xml"><a href="#schema-xml" class="headerlink" title="schema.xml"></a>schema.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version=&quot;1.0&quot;?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="keyword">mycat</span>:schema <span class="keyword">SYSTEM</span> <span class="string">&quot;schema.dtd&quot;</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mycat:schema</span> <span class="attr">xmlns:mycat</span>=<span class="string">&quot;http://io.mycat/&quot;</span>&gt;</span></span><br><span class="line">	<span class="comment">&lt;!--1.逻辑表和逻辑库设置--&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">schema</span> <span class="attr">name</span>=<span class="string">&quot;V_SHOP&quot;</span> <span class="attr">checkSQLschema</span>=<span class="string">&quot;false&quot;</span> <span class="attr">sqlMaxLimit</span>=<span class="string">&quot;100&quot;</span>&gt;</span></span><br><span class="line">       <span class="comment">&lt;!--全局表--&gt;</span></span><br><span class="line">	   <span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_areas&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1,dn2,dn3,dn4&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;areaid&quot;</span> <span class="attr">type</span>=<span class="string">&quot;global&quot;</span>/&gt;</span></span><br><span class="line">	   <span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_provinces&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1,dn2,dn3,dn4&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;provinceid&quot;</span> <span class="attr">type</span>=<span class="string">&quot;global&quot;</span>/&gt;</span></span><br><span class="line">	   <span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_cities&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1,dn2,dn3,dn4&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;cityid&quot;</span>  <span class="attr">type</span>=<span class="string">&quot;global&quot;</span>/&gt;</span></span><br><span class="line">	   <span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_dictionary&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1,dn2,dn3,dn4&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span>  <span class="attr">type</span>=<span class="string">&quot;global&quot;</span>/&gt;</span></span><br><span class="line">		<span class="comment">&lt;!--v_goods商品模块--&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_brand&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> /&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_category&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> /&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_sku&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> /&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_spu&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn1&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> /&gt;</span></span><br><span class="line">        <span class="comment">&lt;!--v_order订单模块--&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_order&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn2&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> /&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_order_item&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn2&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> /&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_order_log&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn2&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> /&gt;</span></span><br><span class="line">		<span class="comment">&lt;!--v_log日志模块--&gt;</span></span><br><span class="line">        <span class="comment">&lt;!--分表规则采用hash一致&quot;log-sharding-by-murmur&quot;规则--&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">table</span> <span class="attr">name</span>=<span class="string">&quot;tb_operatelog&quot;</span> <span class="attr">dataNode</span>=<span class="string">&quot;dn3,dn4&quot;</span> <span class="attr">primaryKey</span>=<span class="string">&quot;id&quot;</span> <span class="attr">rule</span>=<span class="string">&quot;log-sharding-by-murmur&quot;</span>/&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">schema</span>&gt;</span></span><br><span class="line">    </span><br><span class="line">	<span class="comment">&lt;!--2.实际数据节点映射关系--&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">&quot;dn1&quot;</span> <span class="attr">dataHost</span>=<span class="string">&quot;host1&quot;</span> <span class="attr">database</span>=<span class="string">&quot;v_goods&quot;</span> /&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">&quot;dn2&quot;</span> <span class="attr">dataHost</span>=<span class="string">&quot;host2&quot;</span> <span class="attr">database</span>=<span class="string">&quot;v_order&quot;</span> /&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">&quot;dn3&quot;</span> <span class="attr">dataHost</span>=<span class="string">&quot;host3&quot;</span> <span class="attr">database</span>=<span class="string">&quot;v_log&quot;</span> /&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataNode</span> <span class="attr">name</span>=<span class="string">&quot;dn4&quot;</span> <span class="attr">dataHost</span>=<span class="string">&quot;host4&quot;</span> <span class="attr">database</span>=<span class="string">&quot;v_log&quot;</span> /&gt;</span></span><br><span class="line">    </span><br><span class="line">	<span class="comment">&lt;!--3.实际数据节点设置--&gt;</span></span><br><span class="line">    <span class="comment">&lt;!--下面只是有的节点进行分表，并没有进行读写分离--&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">&quot;host1&quot;</span> <span class="attr">maxCon</span>=<span class="string">&quot;1000&quot;</span> <span class="attr">minCon</span>=<span class="string">&quot;10&quot;</span> <span class="attr">balance</span>=<span class="string">&quot;0&quot;</span></span></span><br><span class="line"><span class="tag">		<span class="attr">writeType</span>=<span class="string">&quot;0&quot;</span> <span class="attr">dbType</span>=<span class="string">&quot;mysql&quot;</span> <span class="attr">dbDriver</span>=<span class="string">&quot;native&quot;</span> <span class="attr">switchType</span>=<span class="string">&quot;1&quot;</span>  <span class="attr">slaveThreshold</span>=<span class="string">&quot;100&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span></span><br><span class="line">        <span class="comment">&lt;!--上面说的ip1就是指&quot;192.168.192.158&quot;,下面同理不再赘述--&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">&quot;hostM1&quot;</span> <span class="attr">url</span>=<span class="string">&quot;192.168.192.158:3306&quot;</span> <span class="attr">user</span>=<span class="string">&quot;root&quot;</span> <span class="attr">password</span>=<span class="string">&quot;itcast&quot;</span>&gt;</span>	<span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span>	</span><br><span class="line">    </span><br><span class="line">    <span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">&quot;host2&quot;</span> <span class="attr">maxCon</span>=<span class="string">&quot;1000&quot;</span> <span class="attr">minCon</span>=<span class="string">&quot;10&quot;</span> <span class="attr">balance</span>=<span class="string">&quot;0&quot;</span></span></span><br><span class="line"><span class="tag">		<span class="attr">writeType</span>=<span class="string">&quot;0&quot;</span> <span class="attr">dbType</span>=<span class="string">&quot;mysql&quot;</span> <span class="attr">dbDriver</span>=<span class="string">&quot;native&quot;</span> <span class="attr">switchType</span>=<span class="string">&quot;1&quot;</span>  <span class="attr">slaveThreshold</span>=<span class="string">&quot;100&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">&quot;hostM2&quot;</span> <span class="attr">url</span>=<span class="string">&quot;192.168.192.159:3306&quot;</span> <span class="attr">user</span>=<span class="string">&quot;root&quot;</span> <span class="attr">password</span>=<span class="string">&quot;itcast&quot;</span>&gt;</span><span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span>	</span><br><span class="line">    </span><br><span class="line">    <span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">&quot;host3&quot;</span> <span class="attr">maxCon</span>=<span class="string">&quot;1000&quot;</span> <span class="attr">minCon</span>=<span class="string">&quot;10&quot;</span> <span class="attr">balance</span>=<span class="string">&quot;0&quot;</span></span></span><br><span class="line"><span class="tag">		<span class="attr">writeType</span>=<span class="string">&quot;0&quot;</span> <span class="attr">dbType</span>=<span class="string">&quot;mysql&quot;</span> <span class="attr">dbDriver</span>=<span class="string">&quot;native&quot;</span> <span class="attr">switchType</span>=<span class="string">&quot;1&quot;</span>  <span class="attr">slaveThreshold</span>=<span class="string">&quot;100&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">&quot;hostM3&quot;</span> <span class="attr">url</span>=<span class="string">&quot;192.168.192.160:3306&quot;</span> <span class="attr">user</span>=<span class="string">&quot;root&quot;</span> <span class="attr">password</span>=<span class="string">&quot;itcast&quot;</span>&gt;</span><span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span>	</span><br><span class="line">	</span><br><span class="line">	<span class="tag">&lt;<span class="name">dataHost</span> <span class="attr">name</span>=<span class="string">&quot;host4&quot;</span> <span class="attr">maxCon</span>=<span class="string">&quot;1000&quot;</span> <span class="attr">minCon</span>=<span class="string">&quot;10&quot;</span> <span class="attr">balance</span>=<span class="string">&quot;0&quot;</span></span></span><br><span class="line"><span class="tag">		<span class="attr">writeType</span>=<span class="string">&quot;0&quot;</span> <span class="attr">dbType</span>=<span class="string">&quot;mysql&quot;</span> <span class="attr">dbDriver</span>=<span class="string">&quot;native&quot;</span> <span class="attr">switchType</span>=<span class="string">&quot;1&quot;</span>  <span class="attr">slaveThreshold</span>=<span class="string">&quot;100&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">heartbeat</span>&gt;</span>select user()<span class="tag">&lt;/<span class="name">heartbeat</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">writeHost</span> <span class="attr">host</span>=<span class="string">&quot;hostM4&quot;</span> <span class="attr">url</span>=<span class="string">&quot;192.168.192.161:3306&quot;</span> <span class="attr">user</span>=<span class="string">&quot;root&quot;</span> <span class="attr">password</span>=<span class="string">&quot;itcast&quot;</span>&gt;</span><span class="tag">&lt;/<span class="name">writeHost</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">dataHost</span>&gt;</span>	</span><br><span class="line"><span class="tag">&lt;/<span class="name">mycat:schema</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h3 id="server-xml-1"><a href="#server-xml-1" class="headerlink" title="server.xml"></a>server.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">user</span> <span class="attr">name</span>=<span class="string">&quot;root&quot;</span> <span class="attr">defaultAccount</span>=<span class="string">&quot;true&quot;</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;schemas&quot;</span>&gt;</span>V_SHOP<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;password&quot;</span>&gt;</span>123456<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">user</span>&gt;</span></span><br></pre></td></tr></table></figure>

<h3 id="rule-xml-1"><a href="#rule-xml-1" class="headerlink" title="rule.xml"></a>rule.xml</h3><figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">&lt;!--一致性hash算法 2个库--&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">tableRule</span> <span class="attr">name</span>=<span class="string">&quot;log-sharding-by-murmur&quot;</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">rule</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">columns</span>&gt;</span>id<span class="tag">&lt;/<span class="name">columns</span>&gt;</span></span><br><span class="line">        <span class="tag">&lt;<span class="name">algorithm</span>&gt;</span>log-murmur<span class="tag">&lt;/<span class="name">algorithm</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">rule</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">tableRule</span>&gt;</span></span><br><span class="line"></span><br><span class="line"><span class="tag">&lt;<span class="name">function</span> <span class="attr">name</span>=<span class="string">&quot;log-murmur&quot;</span> <span class="attr">class</span>=<span class="string">&quot;io.mycat.route.function.PartitionByMurmurHash&quot;</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;seed&quot;</span>&gt;</span>0<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;count&quot;</span>&gt;</span>2<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;virtualBucketTimes&quot;</span>&gt;</span>160<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">function</span>&gt;</span></span><br></pre></td></tr></table></figure>

</div> 

<script>
    window.onload = detectors();
</script>
    <div class="post-footer">
    <div class="h-line-primary"></div>
    <nav class="post-nav">
        <div class="prev-item">
           
        </div>
        <div class="next-item">
            
                <div class="icon arrow-right"></div>
                <div class="post-link">
                  <a href="/2022/05/16/ceshi/">Next</a>  
                </div>  
            
        </div>
    </nav>
</div>

    
      <div class="post-comment">

    
        <div id="vcomments"></div>
        <script>
            var META = ['nick', 'mail', 'link'];
            var meta = 'nick,mail';
            meta = meta.split(',').filter(item => {
                return META.includes(item);
            });
            new Valine({
                el: '#vcomments',
                appId: 'X7Jbrh9MUCMJpzODTOJJImgc-gzGzoHsz',
                appKey: 'ALE0H1nS33YlJtDpquuDHFPz',
                lang: 'en',
                placeholder: 'Say something',
                avatar: 'mp',
                meta: meta
            })
        </script>    
     

     
    
    

</div>
     
  
</article>
        </div>
      </div>
      
      <div class="footer">
    <div class="flex-container">
        <div class="footer-text">
            
            
            
                Powered by <a target="_blank" rel="noopener" href="https://hexo.io/">Hexo</a> & <a target="_blank" rel="noopener" href="https://github.com/zoeingwingkei/frame/">Frame</a>
                
        </div>
    </div>
</div>

    </div>

  </body>
</html>
